<?php
/**
 * Created by PhpStorm.
 * User: zhan
 * Date: 2017/12/15  10:51
 */
namespace app\admin\controller;

class Export extends Common
{
    public $enterprise_num;
    public $project_num;


    /*
    *   构造函数
    */
    function __construct(){
        parent::__construct();
    }

    /**
    *  excel页面展示
    */
    function index(){
        set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
        //项目分类查询
        $project_type = db('project_type')->select();

        if(request()->isAjax()) {

            vendor("Excel.PHPExcel");

            $objPHPExcel = new \PHPExcel();

            //获取表单上传文件  
            $file = request()->file('file');
            $xl_file_name = input('file_name');

            if($file) {  
                $info = $file->validate(['ext'=>'xlsx,xls'])->move(ROOT_PATH . 'public/static/uploads'); 

                if($info){
                    $exclePath = $info->getSaveName();  //获取文件名
                    $file_suffix_name = explode('.',$exclePath);
                    $file_suffix_name = $file_suffix_name[1];

                    $file_name = ROOT_PATH . 'public/static/uploads/'. $exclePath;   //上传文件的地址
                    $startRow = 3;
                    $endRow = 502;
                    $bool = true;

                    $this->enterprise_num = 0;
                    $this->project_num = 0;

                    $record_data['file_name'] = $xl_file_name;
                    $record_data['time'] = time();
                    db('xls_record')->insert($record_data);
                    $this->record_id = db('xls_record')->getLastInsID();

                    //分批导入
                    do{
                        //excle处理
                        $excel_array = $this->array_excle($file_name, $file_suffix_name, $startRow, $endRow);

                        //数组处理
                        $bool = $this->array_iconv($excel_array);  

                        $startRow += 500;
                        $endRow += 500;
                        ob_flush();
                        flush();
                        usleep(1000); //睡会觉，大量读取，冲击挺大
                    }while($bool);

                    //读取完数据删除本地文件
                    if(file_exists($file_name)){
                        $data = cache('del_excles');
                        $data[] = $file_name;
                        //写入缓存以后,在common类删除.
                        cache('del_excles',$data);
                        
                        //更新该表添加了多少数据
                        $update_record_data['enterprise_num'] = $this->enterprise_num;
                        $update_record_data['project_num'] = $this->project_num;
                        db('xls_record')->where(array('id'=>$this->record_id))->update($update_record_data);

                        unset($this->enterprise_num);
                        unset($this->project_num);
                    }
                    $this->success("操作成功!", url('Export/index'));  
                }else{  
                    // 上传失败获取错误信息 
                    return $this->error($file->getError()); 
                }
            }else{
                return $this->error('请添加文件');
            }
        }
        $record_result = db('xls_record')->order('time desc')->select();
        $this->assign('record_result', $record_result);
        $this->assign('project_type',$project_type);
        return $this->fetch();
    }

    /**
    *excle处理
    */
    function array_excle($file_name, $file_suffix_name, $startRow = 1, $endRow = null){
        ini_set('memory_limit','4072M');    // 临时设置最大内存占用为3G
        set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期

        if($file_suffix_name == 'xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{  
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }

        $objReader->setReadDataOnly(true);

        //如果有指定行数，则设置过滤器
        if ($startRow && $endRow) {
            vendor("Excel.PHPExcels");
            $perf = new \PHPExcels();
            $perf->startRow = $startRow;
            $perf->endRow   = $endRow;
            $objReader->setReadFilter($perf);
        }

        $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8  
        $activeSheet = $obj_PHPExcel->getActiveSheet();
        $total = $activeSheet->getHighestRow(); //总行数
        
        $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
        return $excel_array;
    }

    /**
     *数组处理
     */
    function array_iconv($array)
    { 
      ini_set('memory_limit','4072M');    // 临时设置最大内存占用为3G
      set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
      $data = array();
      $bool = true;
      $type = input('type');

      if(is_array($array)){
            foreach ($array as $key => &$value) {
                $value = implode("|",$value);

                $value = $this->array_iconv_string($value);
                $string = '';

                $value = explode("|", $value);

                if(empty($value[0])){
                    continue;
                }

                //去除空格
                foreach ($value as $k => &$v) {
                    $v = trim($v);
                    //将空格的名称替换掉
                    $v = str_replace(" ","", $v);
                }

                $data['project'][$key]['num'] = !empty($value[0])?$value[0]:'';
                $data['project'][$key]['project'] = !empty($value[2])?$value[2]:'';
                $data['project'][$key]['time/batch'] = !empty($value[3])?$value[3]:'';
                $data['project'][$key]['certificate'] = !empty($value[4])?$value[4]:'';
                $data['project'][$key]['remarks'] = !empty($value[9])?$value[9]:'';
                $data['project'][$key]['subsidy'] = !empty($value[8])?$value[8]:'';
              
                //地址查询
                if(!empty($value[5])){
                    $string=$value['5'].',';
                }

                if(!empty($value[6])){
                    $string .= $value['6'].',';
                }

                if(!empty($value[7])){
                    $string .= $value['7'];
                }
                $string = explode(',', $string);
                $area_where['name'] = array('in',$string);
                
                if($string != ''){            
                    $area_result = db('area')->where($area_where)->field('id,name')->order('id asc')->select();
                    //将重复地址去掉
                    $area_result = $this->getArrayUniqueByKeys($area_result);
                }

                $data['company'][$key]['name'] = !empty($value[1])?$value[1]:'';
                $data['company'][$key]['province'] = isset($area_result[0]['id'])?$area_result[0]['id']:'0';
                $data['company'][$key]['city'] = isset($area_result[1]['id'])?$area_result[1]['id']:'0';
                $data['company'][$key]['area'] = isset($area_result[2]['id'])?$area_result[2]['id']:'0';
            }

            if(isset($data['company'])){
                //企业排查
                $array_company = array_column($data['company'],'name');
                $array_company = array_unique($array_company);
                $where['name'] = array('in',$array_company);
                $company_result = db('xls_company')->where($where)->field("name,id,concat_ws('|',province,city,area) as path")->select();

                $c_value = array_column($company_result,'name');
                $c_key = array_column($company_result,'id');
                $path = array_column($company_result,'path');

                $in_array=array_combine($c_key,$c_value);
                $in_path_array=array_combine($c_key,$path);

                //项目对应排查
                $array_certificate = array_column($data['project'],'certificate');
                $project = array_column($data['project'],'project');

                $where_project['certificate_no'] = array('in',$array_certificate);
                $where_item_name['item_name'] = array('in',$project);
                
                $xls_project_result = db('xls_project')->where($where_project)->whereor($where_item_name)->field('certificate_no,item_name,company_id,time_batch')->select();

                $certificate_no_result = array_column($xls_project_result,'certificate_no');

                //项目过滤
                $company_item_name = [];
                foreach($xls_project_result as $k=>$v){
                    $company_item_name[$v['company_id']]['item_name'][] = $v['item_name']; 
                    $company_item_name[$v['company_id']][$v['item_name']][] = $v['time_batch']; 
                }

                foreach($data['company'] as $z=>$i){
                        $province = $i['province'];
                        $city = $i['city'];
                        $area = $i['area'];

                        if(!in_array($i['name'], $c_value)){
                            $company_data = array();
                            $company_data['name'] = $i['name'];
                            $company_data['province'] = $province;
                            $company_data['city'] = $city;
                            $company_data['area'] = $area;
                            $company_data['sid'] = 1;
                            db('xls_company')->insert($company_data);
                            $id = db('xls_company')->getLastInsID();
                            //记录新添加了多少个企业
                            $this->enterprise_num++;

                            //防止一个excls表格有相同数据
                            $c_value[$id] = $i['name'];
                            $in_array[$id] = $i['name'];
                        }else{
                            $id = array_search($i['name'], $in_array);
                            if(isset($in_path_array[$id])){
                                //获取企业地址
                                $path_string = $in_path_array[$id];
                                $path_string_array = explode('|', $path_string);

                                //将之前企业空的地址更新上去
                                if(empty($path_string_array[0]) || empty($path_string_array[1]) || empty($path_string_array[2])){

                                    if($province != '0'){
                                        $update_date['province'] = $province;
                                    }
                                    if($city != '0'){ 
                                        $update_date['city'] = $city;
                                    }
                                    if($area != '0'){ 
                                        $update_date['area'] = $area;
                                    }

                                    if(isset($update_date)){
                                        db('xls_company')->where(array('id'=>$id))->update($update_date);
                                        unset($update_date);
                                    }
                                }
                            }
                        }  


                        //项目入库
                        $project_data['item_name'] = $data['project'][$z]['project'];
                        $project_data['time_batch'] = $data['project'][$z]['time/batch'];
                        $project_data['certificate_no'] = $data['project'][$z]['certificate'];
                        $project_data['note'] = $data['project'][$z]['remarks'];
                        $project_data['subsidy_amount'] = $data['project'][$z]['subsidy'] * 1000;
                        $project_data['company_id'] = $id;
                        $project_data['type'] = $type;
                        $project_data['xid'] = $this->record_id;

                        //过滤重复数据
                        if(!empty($project_data['certificate_no'])){
                            //过滤重复的编码号
                            if(in_array($project_data['certificate_no'], $certificate_no_result)){
                                continue;
                            }
                        }else{
                            //过滤已经上传的项目名
                            if(isset($company_item_name[$id]['item_name']) && isset($company_item_name[$id][$project_data['item_name']])){
                                if(in_array($project_data['item_name'], $company_item_name[$id]['item_name']) && in_array($project_data['time_batch'], $company_item_name[$id][$project_data['item_name']])){
                                    continue;
                                }
                            }
                        }

                        $result = db('xls_project')->insert($project_data);
                        if($result)$this->project_num++;
                }
                return true;
            }else{
                return false;
            }
      }else{
        return false;
      }
    }

    //把重复地址筛选掉
    function getArrayUniqueByKeys($arr)  
    {  
        $extends_array = [];
        $arr_wish = [];
        foreach ($arr as $key => $value) {
            $extends_array[$value['id']] = $value['name'];
        }

        $extends_array = array_unique($extends_array);
        $i = 0;
        foreach($extends_array as $key => $value){
            $arr_wish[$i]['id'] = $key;
            $arr_wish[$i]['name'] = $value;
            $i++;
        }

        return $arr_wish;  
    }  

    /**
    *项目分类
    */
    function project_type()
    {
        $project_type_name = db('project_type')->paginate(10);
        $page = $project_type_name->render();

        $this->assign('project_type_name',$project_type_name);
        $this->assign('page',$page);
        return  $this->fetch();
    }

    /**
    *添加项目分类
    */
    function project_type_add()
    {
        $id = input('id');
        $project_type_result = db('project_type')->where(array('id'=>$id))->find();

        if(request()->isAjax()) {
            $name = input('title');

            if($id){
                 db('project_type')->where(array('id'=>$id))->update(array('type_name'=>$name));
            }else{
                db('project_type')->insert(array('type_name'=>$name));
            }

            $this->success('操作成功',url('export/project_type'));
        }

        $this->assign('id',$id);
        $this->assign('project_type_result',$project_type_result);
        return  $this->fetch();
    }

    /**
     * 对数据进行编码转换
     * @param array/string $data 数组
     * @param string $output 转换后的编码
     * Created on 2016-7-13
     */
    function array_iconv_string($data, $output = 'utf-8') {
          $encode_arr = array('UTF-8','ASCII','GBK','GB2312','BIG5','JIS','eucjp-win','sjis-win','EUC-JP');
          $encoded = mb_detect_encoding($data, $encode_arr);
          return mb_convert_encoding($data, $output, $encoded);
    }

    /**
    *   数据删除
    */
    function del_xls_record(){
        $record_id = input('record_id');
        $xls_project = db('xls_project')->where(array('xid'=>$record_id))->delete();
        $xls_record = db('xls_record')->where(array('id'=>$record_id))->delete();

        if(!$xls_record)$this->error('删除失败');
        $this->success('删除成功');
    }
}